#!/usr/bin/python
# -*- encoding: utf-8 -*-
'''
@File    :   1.py
@Time    :   2020/05/12 21:37:42
@Author  :   Malygos_l 
@Version :   1.0
@Contact :   751756061@qq.com
@WebSite :   www.gitee.com/malygos_l
'''
# Start typing your code from here
#  针对我们给大家的2张表（学生表和班级表），做以下查询；（查询脚本放在一个文件中，创建一个SQL文件夹，放到这个里面，最有提交到代码仓库）
# ① 查询所有男生的姓名，年龄，所在班级名称；
# ② 查询所有查询编号小于4或没被删除的学生；
# ③ 查询姓黄并且“名”是一个字的学生；
# ④ 查询编号是1或3或8的学生
# ⑤ 查询编号为3至8的学生
# ⑥ 查询未删除男生信息，按年龄降序
# ⑦  查询女生的总数
# ⑧  查询学生的平均年龄
# ⑨ 分别统计性别为男/女的人年龄平均值
# ⑩ 按照性别来进行人员分组如下显示（group by + group_concat()）；
#   | 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
# 	| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
# 	| 中性   | 金星                                                       |
# 	| 保密   | 凤姐
import pymysql

db = pymysql.connect("localhost", "root", "18311010", "test")

cursor = db.cursor()

# ① 查询所有男生的姓名，年龄，所在班级名称；
try:
    cursor.execute("SELECT * FROM students where gender = '男'")
    re = cursor.fetchall()
    print("所有男生的姓名，年龄，所在班级名称:")
    for ms in re:
        print("{0}\t{1}\t{2}\t".format(ms[1], ms[2], ms[5]))
    print("\n")

except Exception as e:
    raise e

# ② 查询所有查询编号小于4或没被删除的学生；
try:
    cursor.execute("SELECT * FROM students where id < 4 or is_delete = 0")
    re = cursor.fetchall()
    print("所有查询编号小于4或没被删除的学生:")
    for ms in re:
        print("{0}\t{1}".format(ms[0], ms[1]))
    print("\n")

except Exception as e:
    raise e

# ③ 查询姓黄并且“名”是一个字的学生；
try:
    cursor.execute("SELECT * FROM students where name like '黄%'")
    re = cursor.fetchall()
    print("姓黄并且“名”是一个字的学生:")
    for ms in re:
        print("{0}".format(ms[1]))
    print("\n")

except Exception as e:
    raise e

# ④ 查询编号是1或3或8的学生
try:
    cursor.execute("SELECT * FROM students where id = 1 or id = 3 or id = 8")
    re = cursor.fetchall()
    print("1/3/8的学生:")
    for ms in re:
        print("{0}\t{1}".format(ms[0], ms[1]))
    print("\n")

except Exception as e:
    raise e

# ⑤ 查询编号为3至8的学生
try:
    cursor.execute("SELECT * FROM students where id > 2 and id < 9")
    re = cursor.fetchall()
    print("编号3-8的学生:")
    for ms in re:
        print("{0}\t{1}".format(ms[0], ms[1]))
    print("\n")

except Exception as e:
    raise e

# ⑥ 查询未删除男生信息，按年龄降序
try:
    cursor.execute("SELECT * FROM students where gender = '男' and is_delete = 0 order  by age desc ")
    re = cursor.fetchall()
    print("未删除男生信息（按年龄降序）:")
    for ms in re:
        print("{0}\t{1}".format(ms[1], ms[2]))
    print("\n")

except Exception as e:
    raise e

# ⑦  查询女生的总数
try:
    cursor.execute("SELECT * FROM students where gender = '女'")
    re = cursor.fetchall()
    print("女生总数:")
    print("{0}".format(len(re)))
    print("\n")

except Exception as e:
    raise e

# ⑧  查询学生的平均年龄
try:
    cursor.execute("SELECT avg(age) FROM students")
    re = cursor.fetchall()
    print("学生平均年龄:")
    for ms in re:
        print("{0}".format(ms[0]))
    print("\n")

except Exception as e:
    raise e

# ⑨ 分别统计性别为男/女的人年龄平均值
try:
    cursor.execute("SELECT avg(age) FROM students where gender = '男'")
    re = cursor.fetchall()
    print("男生平均年龄:")
    for ms in re:
        print("{0}".format(ms[0]))
    print("\n")

    cursor.execute("SELECT avg(age) FROM students where gender = '女'")
    re = cursor.fetchall()
    print("女生平均年龄:")
    for ms in re:
        print("{0}".format(ms[0]))
    print("\n")

except Exception as e:
    raise e

# ⑩ 按照性别来进行人员分组如下显示(group by + group_concat())
try:
    cursor.execute("SELECT gender, group_concat(name) FROM students group by gender")
    re = cursor.fetchall()
    print("按性别分组:")
    for ms in re:
        print("{0}".format(ms))
    print("\n")

except Exception as e:
    raise e

# for i in cursor:
#     print(i)

# 关闭
cursor.close()
db.close()
